from openpyxl import load_workbook
from openpyxl_image_loader import SheetImageLoader
import os
from datetime import datetime
from openpyxl.utils import range_boundaries
from util.common import folder_path
import numpy as np
from PIL import Image

#来源：https://blog.csdn.net/weixin_44380466/article/details/131864657
#来源：https://blog.csdn.net/weixin_46783197/article/details/139320666
folder_path = folder_path('excel')

# 定义一个每个表格有多少数据的
excel_total_data = [175,139,58,9,24,31,39,66,19,17]

# 选择工作表
workbook = load_workbook('../upload/excel/sample/product.xlsx')
sheet_names = workbook.sheetnames
for key,item in enumerate(sheet_names):

    sheet = workbook[item]
    print(sheet,item)
    image_loader = SheetImageLoader(sheet)

    path = folder_path + "/" + item
    if not os.path.exists(path):
        os.makedirs(path)

    # 获取合并单元格数据的最大值和最小值
    merged_cell_range = list(sheet.merged_cells.ranges)
    merged_cell_C2 = [cell for cell in merged_cell_range if cell.coord[0]=='C']
    merged_data = []
    for merged_cell in merged_cell_C2:
        min_col, min_row, max_col, max_row = merged_cell.bounds
        obj = {'min':min_row,'max':max_row} 
        merged_data.append(obj)
    print(item,"merge_data",merged_data)

    # excel表中id,barcode字典
    excel_barcodeList = []
    for index, row in enumerate(sheet.rows, start=1):
        name = row[3].value
        if name != None :
            if '\n' in name:
                name = name.split('\n')[0]
        data = {"id": index,"barcode":name}
        if index < excel_total_data[key]:
            excel_barcodeList.append(name)
    print(excel_barcodeList,len(excel_barcodeList))

         
    images = sheet._images
    image_positions = {}
    output_folder = 'test/'

    print(excel_barcodeList) 
    img_arr = []
    for image in images:
        img_row = image.anchor.to.row
        img_column = image.anchor.to.col
        ts = str(img_row) + "-" + str(img_column)
        if ts in img_arr:
            if excel_barcodeList[img_row]:
                pic_name = excel_barcodeList[img_row] + "_1.png"
        else:
            img_arr.append(ts)
            if excel_barcodeList[img_row]:
                pic_name = excel_barcodeList[img_row]+".png"
       
        print("pic_name",item,img_row,pic_name,img_arr,len(img_arr))
        data = image.ref
        with open(os.path.join(output_folder, pic_name), 'wb') as img_file:
            img_pil = Image.open(image.ref).convert("RGB")
            img_array = np.array(img_pil)
            # 将 NumPy 数组保存为图像文件
            img_pil.save(os.path.join(output_folder, pic_name))

        for item in merged_data:
            img_row = img_row + 1
            if(img_row >= item['min'] and img_row <= item['max']):
                for i in range(item['min'], item['max']+1):
                    name = sheet[f'D{i}'].value
                    if name != None :
                        if '\n' in name:
                            name = name.split('\n')[0]
                        data = image.ref
                        pic_name = name +".png"
                        print(pic_name)
                        with open(os.path.join(output_folder, pic_name), 'wb') as img_file:
                            img_pil = Image.open(image.ref).convert("RGB")
                            img_array = np.array(img_pil)
                            # 将 NumPy 数组保存为图像文件
                            # pic_name = excel_barcodeList[img_row]
                            img_pil.save(os.path.join(output_folder, pic_name))
    workbook.close()

